The goal of this notebook is to introduce you to the Protein expression BigQuery table.
This table contains all available TCGA Level-3 protein expression data produced by MD Anderson's RPPA pipeline, as of July 2016. The most recent archives (eg mdanderson.org_COAD.MDA_RPPA_Core.Level_3.2.0.0) for each of the 32 tumor types was downloaded from the DCC, and data extracted from all files matching the pattern %_RPPA_Core.protein_expression%.txt. Each of these “protein expression” files has two columns: the Composite Element REF and the Protein Expression. In addition, each mage-tab archive contains an antibody_annotation file which is parsed in order to obtain the correct mapping between antibody name, protein name, and gene symbol. During the ETL process, portions of the protein name and the antibody name were extracted into additional columns in the table, including Phospho, antibodySource and validationStatus.
In order to work with BigQuery, you need to import the python bigquery module (gcp.bigquery) and you need to know the name(s) of the table(s) you are going to be working with:
In [1]:
import gcp.bigquery as bq
rppa_BQtable = bq.Table('isb-cgc:tcga_201607_beta.Protein_RPPA_data')
From now on, we will refer to this table using this variable ($rppa_BQtable), but we could just as well explicitly give the table name each time.
Let's start by taking a look at the table schema:
In [2]:
%bigquery schema --table $rppa_BQtable
Out[2]:
Let's count up the number of unique patients, samples and aliquots mentioned in this table. We will do this by defining a very simple parameterized query. (Note that when using a variable for the table name in the FROM clause, you should not also use the square brackets that you usually would if you were specifying the table name as a string.)
In [3]:
%%sql --module count_unique
DEFINE QUERY q1
SELECT COUNT (DISTINCT $f, 25000) AS n
FROM $t
In [4]:
fieldList = ['ParticipantBarcode', 'SampleBarcode', 'AliquotBarcode']
for aField in fieldList:
field = rppa_BQtable.schema[aField]
rdf = bq.Query(count_unique.q1,t=rppa_BQtable,f=field).results().to_dataframe()
print " There are %6d unique values in the field %s. " % ( rdf.iloc[0]['n'], aField)
In [5]:
fieldList = ['Gene_Name', 'Protein_Name', 'Protein_Basename']
for aField in fieldList:
field = rppa_BQtable.schema[aField]
rdf = bq.Query(count_unique.q1,t=rppa_BQtable,f=field).results().to_dataframe()
print " There are %6d unique values in the field %s. " % ( rdf.iloc[0]['n'], aField)
Based on the counts, we can see that there are several genes for which multiple proteins are assayed, and that overall this dataset is quite small compared to most of the other datasets. Let's look at which genes have multiple proteins assayed:
In [6]:
%%sql
SELECT
Gene_Name,
COUNT(*) AS n
FROM (
SELECT
Gene_Name,
Protein_Name,
FROM
$rppa_BQtable
GROUP BY
Gene_Name,
Protein_Name )
GROUP BY
Gene_Name
HAVING
( n > 1 )
ORDER BY
n DESC
Out[6]:
Let's look further in the the EIF4EBP1 gene which has the most different proteins being measured:
In [7]:
%%sql
SELECT
Gene_Name,
Protein_Name,
Phospho,
antibodySource,
validationStatus
FROM
$rppa_BQtable
WHERE
( Gene_Name="EIF4EBP1" )
GROUP BY
Gene_Name,
Protein_Name,
Phospho,
antibodySource,
validationStatus
ORDER BY
Gene_Name,
Protein_Name,
Phospho,
antibodySource,
validationStatus
Out[7]:
Some antibodies are non-specific and bind to protein products from multiple genes in a gene family. One example of this is the AKT1, AKT2, AKT3 gene family. This non-specificity is indicated in the antibody-annotation file by a list of gene symbols, but in this table, we duplicate the entries (as well as the data values) on multiple rows:
In [8]:
%%sql
SELECT
Gene_Name,
Protein_Name,
Phospho,
antibodySource,
validationStatus
FROM
$rppa_BQtable
WHERE
( Gene_Name CONTAINS "AKT" )
GROUP BY
Gene_Name,
Protein_Name,
Phospho,
antibodySource,
validationStatus
ORDER BY
Gene_Name,
Protein_Name,
Phospho,
antibodySource,
validationStatus
Out[8]:
In [9]:
%%sql
SELECT
SampleBarcode,
Study,
Gene_Name,
Protein_Name,
Protein_Expression
FROM
$rppa_BQtable
WHERE
( Protein_Name="Akt" )
ORDER BY
SampleBarcode,
Gene_Name
LIMIT
9
Out[9]:
In [ ]: